const date = require('../utils/date');

const mysql = require('../db/mysql57');

const devDao = {
    addArgvVersion(reqId, uid, version, count, content, type) {
        let sql = `insert into ArgVersion(version, count, content, type) values(?, ?, ?, ?)`;
        let data = [ version, count, content, type ];
        return mysql.sqlExec(sql, data);
    },
    getAllArgvVersion(reqId, uid) {
        let sql = `select id, version, count, content, type from ArgVersion where is_del = ? order by id desc`;
        let data = [ 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getArgvVersionInfo(reqId, uid, id) {
        let sql = `select id, version, count, content, type from ArgVersion where id = ? and is_del = ?`;
        let data = [ id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    copyCreateArgvVersion(reqId, uid, id, version) {
        let sql = `insert into ArgVersion(version, count, content) (
                select ?, count, content from ArgVersion where id = ? and is_del = ?
            )`;
        let data = [ version, id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getArgvVersionUsed(reqId, uid, id) {
        let sql = `select
                d.id as id,
                d.devID as devID,
                dt.name as name,
                dp.hospital as hospital,
                dp.name as department
            from Device as d
            left join DeviceType as dt on d.deviceTypeId = dt.id
            left join Department as dp on d.departmentId = dp.id
            where d.argversionId = ? and d.is_del = ?`;
        let data = [ id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    async deleteArgvVersion(reqId, uid, id) {
        let connection, ret, result;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务失败`);
            await mysql.rollbackTransaction(connection);
            throw new Error("连接数据库失败 请联系系统管理员");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务成功`);
        // 查询这个参数版本有没有被使用
        try {
            let sql = `select count(id) as s from Device where argversionId = ? and is_del = ?`;
            let data = [ id, 'false' ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询参数版本有没有被使用失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("查询参数版本有没有被使用失败");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询参数版本有没有被使用成功 ${ JSON.stringify(result) }`);
        if (result[0].s > 0) {
            throw new Error("被删除参数版本还有设备在使用 请先进行相关处理");
        }
        try {
            let sql = `update ArgVersion set is_del = ? where id = ?`;
            let data = [ 'true', id ];
            ret = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除参数版本失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("删除参数版本失败 请稍后再试 或 联系管理员排查问题");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除参数版本成功`);
        // 所有操作成功 提交事务
        await mysql.commitTransaction(connection);
        return ret;
    },
    updateArgvVersion(reqId, uid, id, version, count, content, type) {
        let sql = `update ArgVersion set version = ?, count = ?, content = ?, type = ? where id = ? and is_del = ?`;
        let data = [ version, count, content, type, id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    addDevType(reqId, uid, type, name) {
        let sql = `insert into DeviceType(type, name) values(?, ?)`;
        let data = [ type, name ];
        return mysql.sqlExec(sql, data);
    },
    getAllDevType(reqId, uid) {
        let sql = `select
                id, type, name,
                FORMAT(createTime, '%Y-%m-%d %H:%i:%s') as createTime,
                FORMAT(updateTime, '%Y-%m-%d %H:%i:%s') as updateTime
            from DeviceType
            where is_del = ?
            order by id desc`;
        let data = [ 'false' ];
        return mysql.sqlExec(sql, data);
    },
    updateDevType(reqId, uid, devTypeId, type, name) {
        let sql = `update DeviceType set type = ?, name = ? where id = ? and is_del = ?`;
        let data = [ type, name, devTypeId, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getDevTypeUsed(reqId, uid, id) {
        let sql = `select
                d.id as id,
                d.devID as devID,
                dt.name as name,
                dp.hospital as hospital,
                dp.name as department
            from Device as d
            left join DeviceType as dt on d.deviceTypeId = dt.id
            left join Department as dp on d.departmentId = dp.id
            where d.deviceTypeId = ? and d.is_del = ?`;
        let data = [ id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    async deleteDevType(reqId, uid, id) {
        let connection, ret, result;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务失败`);
            await mysql.rollbackTransaction(connection);
            throw new Error("连接数据库失败 请联系系统管理员");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务成功`);
        // 查询这个型号系列有没有被使用
        try {
            let sql = `select count(id) as s from Device where deviceTypeId = ? and is_del = ?`;
            let data = [ id, 'false' ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询型号系列有没有被使用失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("查询型号系列有没有被使用失败");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询型号系列有没有被使用成功 ${ JSON.stringify(result) }`);
        if (result[0].s > 0) {
            throw new Error("被删除型号系列还有设备在使用 请先进行相关处理");
        }
        try {
            let sql = `update DeviceType set is_del = ?, del_pk = ? where id = ?`;
            let data = [ 'true', id, id ];
            ret = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除型号系列失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("删除型号系列失败 请稍后再试 或 联系管理员排查问题");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除型号系列成功`);
        // 所有操作成功 提交事务
        await mysql.commitTransaction(connection);
        return ret;
    },
    addDevice(reqId, uid, departmentId, deviceTypeId, devID, devPswd, enzymeSolution, disinfectant) {
        let sql, data;
        if (departmentId) {
            sql = `insert into Device(
                    departmentId,
                    argversionId,
                    deviceTypeId,
                    devID,
                    devPswd,
                    enableDatetime,
                    enzymeSolution,
                    disinfectant,
                    argv
                ) VALUES (?, (
                    select id from ArgVersion where version = ?
                ), ?, ?, ?, ?, ?, ?, ?)`;
            data = [ departmentId, '未收到版本信息', deviceTypeId, devID, devPswd, date.toLocaleString(new Date()), enzymeSolution, disinfectant, '[]' ];
        } else {
            sql = `insert into Device(
                    argversionId,
                    deviceTypeId,
                    devID,
                    devPswd,
                    enzymeSolution,
                    disinfectant,
                    argv
                ) VALUES ((
                    select id from ArgVersion where version = ?
                ), ?, ?, ?, ?, ?, ?)`;
            data = [ '未收到版本信息', deviceTypeId, devID, devPswd, enzymeSolution, disinfectant, '[]' ];
        }
        return mysql.sqlExec(sql, data);
    },
    getDeviceList(reqId, uid) {
        let sql = `select
                d.id as id,
                d.devID as devId,
                d.deviceTypeId as devTypeId,
                dt.type as devType,
                dt.name as devTypeName,
                d.argversionId as argvId,
                av.version as argvVersion,
                d.run_status as run_status,
                c.provinceId as provinceId,
                p.name as province,
                dp.cityId as cityId,
                c.name as city,
                dp.hospital as hospital,
                d.departmentId as departmentId,
                dp.name as department
            from Device as d
            left join DeviceType as dt on d.deviceTypeId = dt.id
            left join ArgVersion as av on d.argversionId = av.id
            left join Department as dp on d.departmentId = dp.id
            left join City as c on dp.cityId = c.id
            left join Province as p on c.provinceId = p.id
            where d.is_del = ?
            order by d.id desc`;
        let data = [ 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getDeviceInfo(reqId, uid, id) {
        let sql = `select
                d.id as id,
                d.devID as devId,
                d.deviceTypeId as devTypeId,
                dt.type as devType,
                dt.name as devTypeName,
                d.argversionId as argvId,
                av.version as argvVersion,
                d.run_status as run_status,
                d.ctrl_type as ctrl_type,
                d.devPswd as devPswd,
                DATE_FORMAT(d.enableDatetime, '%Y-%m-%d %H:%i:%s') as enableDatetime,
                d.changeDisinfectantCount as changeDisinfectantCount,
                d.totalUseCount as totalUseCount,
                d.enzymeSolution as enzymeSolution,
                d.disinfectant as disinfectant,
                DATE_FORMAT(d.warrantyStartDate, '%Y-%m-%d') as warrantyStartDate,
                DATE_FORMAT(d.warrantyEndDate, '%Y-%m-%d') as warrantyEndDate,
                c.provinceId as provinceId,
                p.name as province,
                dp.cityId as cityId,
                c.name as city,
                dp.hospital as hospital,
                d.departmentId as departmentId,
                dp.name as department,
                d.openRate as openRate
            from ( select * from Device where id = ? and is_del = ? ) as d
            left join DeviceType as dt on d.deviceTypeId = dt.id
            left join ArgVersion as av on d.argversionId = av.id
            left join Department as dp on d.departmentId = dp.id
            left join City as c on dp.cityId = c.id
            left join Province as p on c.provinceId = p.id`;
        let data = [ id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    async deleteDevice(reqId, uid, id) {
        let connection, ret, result;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务失败`);
            await mysql.rollbackTransaction(connection);
            throw new Error("连接数据库失败 请联系系统管理员");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务成功`);
        // 删除设备
        try {
            let sql = `update Device set is_del = ?, del_pk = ? where id = ?`;
            let data = [ 'true', id, id ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除设备失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("删除设备失败");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除设备 ${ JSON.stringify(result) }`);
        // 删除洗消记录
        try {
            let sql = `update DeviceWash set is_del = ? where deviceId = ?`;
            let data = [ 'true', id ];
            ret = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除洗消记录失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("删除洗消记录失败 请稍后再试 或 联系管理员排查问题");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除洗消记录成功`);
        // 删除故障记录
        try {
            let sql = `update DeviceFault set is_del = ? where deviceId = ?`;
            let data = [ 'true', id ];
            ret = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除故障记录失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("删除故障记录失败 请稍后再试 或 联系管理员排查问题");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除故障记录成功`);
        // 删除维修记录
        try {
            let sql = `update DeviceRepair set is_del = ? where deviceId = ?`;
            let data = [ 'true', id ];
            ret = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除维修记录失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("删除维修记录失败 请稍后再试 或 联系管理员排查问题");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除维修记录成功`);
        // 所有操作成功 提交事务
        await mysql.commitTransaction(connection);
        return result;
    },
    getDeviceArgv(reqId, uid, id) {
        let sql = `select
                d.id as id,
                d.argv as valueList,
                av.version as argvVersion,
                av.count as argvCount,
                av.content as keyList
            from ( select * from Device where id = ? and is_del = ? ) as d
            left join ArgVersion as av on d.argversionId = av.id`;
        let data = [ id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    ctrlReadArgv(reqId, uid, id) {
        let sql = `update Device set ctrl_type = ? where id = ? and is_del = ?`;
        let data = [ 'GetPara', id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    ctrlSetStatus(reqId, uid, id, status) {
        let sql = `update Device set run_status = ?, ctrl_type = ? where id = ? and is_del = ?`;
        let data = [ status, 'RUN', id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    ctrlSetPassword(reqId, uid, id, password) {
        let sql = `update Device set ctrl_type = ?, ctrl_setPassword = ? where id = ? and is_del = ?`;
        let data = [ 'SetPass', password, id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    async ctrlCancel(reqId, uid, id) {
        let connection, ret, result;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务失败`);
            await mysql.rollbackTransaction(connection);
            throw new Error("连接数据库失败 请联系系统管理员");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务成功`);
        // 查询这个设备当前有什么操作
        try {
            let sql = `select ctrl_type, run_status from Device where id = ? and is_del = ?`;
            let data = [ id, 'false' ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询当前设备的控制指令失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("查询当前设备的控制指令失败");
        }
        if (result.length) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询当前设备的控制指令成功 ${ JSON.stringify(result) }`);
        } else {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 该设备不存在`);
            await mysql.rollbackTransaction(connection);
            throw new Error("该设备不存在");
        }
        let ctrl_type = result[0].ctrl_type;
        let run_status = result[0].run_status;
        if (ctrl_type == 'RUN') {
            try {
                let sql = `update Device set run_status = ?, ctrl_type = ? where id = ?`;
                let data = [ (run_status == 'Enable' ? 'Disable' : 'Enable'), null, id ];
                ret = await mysql.sqlExecInTransaction(connection, sql, data);
            } catch (err) {
                console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 取消设备操作失败 ${ err.message }`);
                await mysql.rollbackTransaction(connection);
                throw new Error("取消设备操作失败 请稍后再试 或 联系管理员排查问题");
            }
        } else {
            try {
                let sql = `update Device set ctrl_type = ? where id = ?`;
                let data = [ null, id ];
                ret = await mysql.sqlExecInTransaction(connection, sql, data);
            } catch (err) {
                console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 取消设备操作失败 ${ err.message }`);
                await mysql.rollbackTransaction(connection);
                throw new Error("取消设备操作失败 请稍后再试 或 联系管理员排查问题");
            }
        }
        // 所有操作成功 提交事务
        await mysql.commitTransaction(connection);
        return ret;
    },
    updateDevDepartment(reqId, uid, id, departmentId) {
        let sql = `update Device set departmentId = ?, enableDatetime = ? where id = ? and is_del = ?`;
        let data = [  departmentId, date.toLocaleString(new Date()), id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    updateDevInfo(reqId, uid, id, enzymeSolution, disinfectant, warrantyStartDate, warrantyEndDate) {
        let sql = `update Device
            set enzymeSolution = ?,
                disinfectant = ?,
                warrantyStartDate = ?,
                warrantyEndDate = ?
            where id = ? and is_del = ?`;
        let data = [ enzymeSolution, disinfectant, warrantyStartDate, warrantyEndDate, id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    updateDevOpenRate(reqId, uid, id, openRate) {
        let sql = `update Device
            set openRate = ?
            where id = ? and is_del = ?`;
        let data = [ openRate, id, 'false' ];
        return mysql.sqlExec(sql, data);
    }
};

module.exports = devDao;